drop procedure dbo.spAPList
go

create procedure spAPList
(
	@UserId uniqueidentifier
)
AS
BEGIN
	set nocount on
	set xact_abort on
	
	DECLARE @uId varchar(10) = '0';
	DECLARE @UserName varchar(256);
	DECLARE @DepName varchar(50) = '1';
	DECLARE @isAdmin varchar(1) = '0';
	DECLARE @isFullAccess varchar(1) ='0';
	SET @UserName = (select UserName From dbo.aspnet_Users where UserId = @UserId);
	EXEC @isAdmin = dbo.aspnet_UsersInRoles_IsUserInRole @ApplicationName = '/', @UserName = @UserName, @RoleName ='admin' 
 
	IF @isAdmin <> 1
	BEGIN
		SET @isFullAccess = (select [User].IsFullAccess From dbo.[User] where UserId = @UserId);
		IF @isFullAccess <> 1
		BEGIN
			SET @uId = (select [User].Id From dbo.[User] where UserId = @UserId);
			SET @DepName = 'AP.' + (select APColumnName from dbo.Depart where DepartId = (select [User].DepartId From dbo.[User] where Id = @uId)) + 'UserId'
		End
	END	
    
        DECLARE @sql NVARCHAR(MAX) = ''
	SET @sql = 'select AP.*,
			sp.StorePointName, sch.SaleChannelName, 
			ud1.FullName as Depart1UserName,
			ud2.FullName as Depart2UserName,
			ud3.FullName as Depart3UserName,
			ud4.FullName as Depart4UserName,
			ud5.FullName as Depart5UserName
	from AP (nolock) left join StorePoint (nolock) as sp on AP.StorePointId = sp.StorePointId
					 left join SaleChannel (nolock) as sch on AP.SaleChannelId = sch.SaleChannelId
					 left join [User] as ud1 on AP.Depart1UserId = ud1.Id
					 left join [User] as ud2 on AP.Depart2UserId = ud2.Id
					 left join [User] as ud3 on AP.Depart3UserId = ud3.Id
					 left join [User] as ud4 on AP.Depart4UserId = ud4.Id
					 left join [User] as ud5 on AP.Depart5UserId = ud5.Id
	where AP.IsDelete = 0 and (' + @isAdmin + ' = 1 or ' + @isFullAccess + ' = 1 or ' + @DepName +' = '+ @uId +')';

	EXEC sp_executesql @sql
END
